package com.vb.webpress.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.vb.webpress.data.Constants.NewsColum;

public class NewsDAO {
	public static boolean insert(News news) throws Exception{

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = DbManager.getConnection();
			pstm = conn.prepareStatement(NewsColum.NEWS_TABLE_INSERT);
			
			pstm.setString(1, news.getType_name());
			pstm.setTimestamp(2, news.getTime());
			pstm.setString(3, news.getPic_uris());
			pstm.setString(4, news.getTitle());
			pstm.setString(5, news.getContent());
			pstm.setString(6, news.getAttachement_uris());
				
			return pstm.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try{
				if(pstm != null)
					pstm.close();
				if(conn != null)
					conn.close();
			}catch(Exception e){
				e.printStackTrace();
			}
			
		}
		return false;
	}
	public static String[] findType(){

		Connection conn = null;
		Statement stm = null;
		try {
			conn = DbManager.getConnection();
			stm = conn.createStatement();
			ResultSet rs = null;
			rs = stm.executeQuery(NewsColum.NEWS_TABLE_FIND_TYPE);
			ArrayList al = new ArrayList();
			
			while(rs.next())
				al.add(rs.getString(NewsColum.TYPE_NAME));
			String[] type = new String[al.size()];
			
			for(int i = 0; i < al.size(); i++)
				type[i] = al.get(i).toString();
			return type;	
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try{
				if(stm != null)
					stm.close();
				if(conn != null)
					conn.close();
			}catch(Exception e){
				e.printStackTrace();
			}
			
		}
		return null;
	}
	
	public static News[] findNewsByType(String type, int count1, int count2) throws Exception{

		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			conn = DbManager.getConnection();
			pstm = conn.prepareStatement(NewsColum.NEWS_TABLE_FIND_BY_TYPE);
			
			pstm.setString(1, type);
			pstm.setInt(2, count1);
			pstm.setInt(3, count2);

			
			rs = pstm.executeQuery();
			News[] newsArray = new News[count2 - count1 + 1];
			int i = 0;
			while(rs.next()){
				newsArray[i] = new News();
				newsArray[i].setId(rs.getInt(NewsColum.ID));
				newsArray[i].setType_name(rs.getString(NewsColum.TYPE_NAME));
				newsArray[i].setTime(rs.getTimestamp(NewsColum.TIME));
				newsArray[i].setPic_uris(rs.getString(NewsColum.PIC_URIS));
				newsArray[i].setTitle(rs.getString(NewsColum.TITLE));
				newsArray[i].setContent(rs.getString(NewsColum.CONTENT));
				newsArray[i].setAttachement_uris(rs.getString(NewsColum.ATTACHEMENT_URIS));
				i++;
			}
			return newsArray;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try{
				if(pstm != null)
					pstm.close();
				if(conn != null)
					conn.close();
			}catch(Exception e){
				e.printStackTrace();
			}
			
		}
		return null;
	}
	public static News[] findAllNewsByType(String type) throws Exception{

		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			conn = DbManager.getConnection();
			pstm = conn.prepareStatement(NewsColum.NEWS_TABLE_FIND_ALL_BY_TYPE);
			
			pstm.setString(1, type);
			System.out.println(pstm.toString());
			rs = pstm.executeQuery();
			ArrayList al = new ArrayList();
			while(rs.next()){
				News news = new News();
				news.setId(rs.getInt(NewsColum.ID));
				news.setType_name(rs.getString(NewsColum.TYPE_NAME));
				news.setTime(rs.getTimestamp(NewsColum.TIME));
				news.setPic_uris(rs.getString(NewsColum.PIC_URIS));
				news.setTitle(rs.getString(NewsColum.TITLE));
				news.setContent(rs.getString(NewsColum.CONTENT));
				news.setAttachement_uris(rs.getString(NewsColum.ATTACHEMENT_URIS));
				al.add(news);
			}
			News[] newsArray = new News[al.size()];
			for(int i = 0; i < al.size(); i++)
				newsArray[i] = (News)al.get(i);
			
			return newsArray;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try{
				if(pstm != null)
					pstm.close();
				if(conn != null)
					conn.close();
			}catch(Exception e){
				e.printStackTrace();
			}
			
		}
		return null;
	}
	public static News findNewsById(int id) throws Exception{

		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		News news = null;
		try {
			conn = DbManager.getConnection();
			pstm = conn.prepareStatement(NewsColum.NEWS_TABLE_FIND_ID);
			
			pstm.setInt(1, id);
			
			System.out.println(pstm.toString());
			rs = pstm.executeQuery();
			if(rs.next()){
				news = new News();
				news.setId(rs.getInt(NewsColum.ID));
				news.setType_name(rs.getString(NewsColum.TYPE_NAME));
				news.setTime(rs.getTimestamp(NewsColum.TIME));
				news.setPic_uris(rs.getString(NewsColum.PIC_URIS));
				news.setTitle(rs.getString(NewsColum.TITLE));
				news.setContent(rs.getString(NewsColum.CONTENT));
				news.setAttachement_uris(rs.getString(NewsColum.ATTACHEMENT_URIS));
			}
			return news;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try{
				if(pstm != null)
					pstm.close();
				if(conn != null)
					conn.close();
			}catch(Exception e){
				e.printStackTrace();
			}
			
		}
		return null;
	}
	
}
